﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.ServiceModel;
using ServiceOfSansar.Models;

namespace ServiceOfSansar
{
    [ServiceContract(Namespace = "Sansar")]
    //[ServiceBehavior(MaxItemsInObjectGraph = 65536000)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Multiple, MaxItemsInObjectGraph = 2147483647)]
    public class Enquiry
    {

        #region Database
        private SansarEntities db;
        public Enquiry()
        {
            db = new SansarEntities();
        }
        ~Enquiry()
        {
            db.Dispose();
        }
        #endregion

        #region Дүүрэг хорооны жагсаалт
        [OperationContract] //Дүүрэг хорооны жагсаалт
        public List<vw_DistAndQuart> DistrictAndQuarterList()
        {
            return db.vw_DistAndQuart.OrderBy(p => p.DistrictCode).ToList();
        }
        [OperationContract] //Horoo жагсаалт
        public List<Quarter> QuarterList(string District)
        {
            return db.Quarters.Where(p => p.DistrictCode.Equals(District)).ToList();
        }

        #endregion

        #region Банкны жагсаалт
        [OperationContract] //Банкны жагсаалт
        public List<Bank> BankList()
        {
            return db.Banks.ToList();
        }

        [OperationContract] //Банкны жагсаалт
        public List<Bank> BankListForSearch()
        {
            List<Bank> banks = db.Banks.ToList();
            Bank b = new Bank();
            b.BankID = 0;
            b.Name = "";
            banks.Add(b);
            return banks.OrderBy(bb => bb.BankID).ToList();
        }
        #endregion

        #region Кассын жагсаалт
        [OperationContract] //Кассын жагсаалт
        public List<BankBranch> BankBranches(int BankID)
        {
            return db.BankBranches.Where(p => p.BankID == BankID).ToList();

        }

        [OperationContract] //Кассын жагсаалт
        public List<BankBranch> BankBranchesForSearch(int BankID)
        {
            List<BankBranch> branches = db.BankBranches.Where(p => p.BankID == BankID).ToList();
            BankBranch b = new BankBranch();
            b.BranchID = 0;
            b.Name = "";
            b.BankID = 0;
            branches.Add(b);
            return branches.OrderBy(pp => pp.BranchID).ToList();
        }
        #endregion

        #region Horoolol
        [OperationContract] //horoolol жагсаалт
        public List<Section> SectionList()
        {
            List<Section> sec = db.Sections.ToList();
            return sec.OrderBy(pp => pp.SectionNo).ToList();
        }
        [OperationContract] //horoolol жагсаалт hailt
        public List<Section> SectionListForSearch()
        {
            List<Section> sec = db.Sections.ToList();
            Section b = new Section();
            b.DistrictCode = "01";
            b.Name = "";
            b.SectionNo = 0;

            sec.Add(b);
            return sec.OrderBy(pp => pp.SectionNo).ToList();
        }

        [OperationContract]
        public List<Section> SectionListByDisctrict(string dis_code)
        {
            List<Section> sec = db.Sections.Where(w => w.DistrictCode == dis_code).ToList();
            return sec.OrderBy(pp => pp.SectionNo).ToList();
        }
        #endregion

        #region Kontor list
        [OperationContract] //kontor жагсаалт
        public List<Kontor> KontorList()
        {
            List<Kontor> kon = db.Kontors.ToList();
            return kon.OrderBy(pp => pp.KontorNo).ToList();
        }
        [OperationContract] //kontor жагсаалт
        public List<Kontor> KontorListForSearch()
        {
            List<Kontor> kon = db.Kontors.ToList();
            Kontor b = new Kontor();
            b.KontorNo = 0;
            b.Name = "";
            kon.Add(b);
            return kon.OrderBy(pp => pp.KontorNo).ToList();
        }
        #endregion

        #region bairnii jagsaalt
        [OperationContract] //Байрны жагсаалт
        public List<sp_GetApartmentList_Result> ApartmentList(global::System.String district, global::System.String quarter, global::System.Int32 bank, global::System.Int32 bankBranch, global::System.Int32 section, global::System.Int32 kontor, global::System.String name, global::System.String apartCode, global::System.String apartNo1, global::System.String parentCode)
        {
            List<sp_GetApartmentList_Result> Aps = db.sp_GetApartmentList(district, quarter, bank,
                bankBranch, section, kontor, name, apartCode, apartNo1, parentCode).ToList();
            return Aps;
        }

        #endregion

        #region Baitsaagchiin jagsaalt
        [OperationContract] //Baitsaagchin jagsaalt
        public List<User> CollectorList()
        {
            List<User> users = db.Users.Where(u => u.PositionNo.Equals("C")).ToList();
            return users;
        }

        #endregion

        [OperationContract] //Хэсгүүдийн мэдээлэл
        public List<Part> PartList()
        {
            return db.Parts.ToList();
        }

        [OperationContract]
        public List<Position> PositionList()
        {
            return db.Positions.ToList();
        }
        [OperationContract]
        public List<District> DistrictList()
        {
            return db.Districts.ToList();
        }
        [OperationContract]
        public List<vw_Billing> BillList(string start, string end)
        {
            return db.ExecuteStoreQuery<vw_Billing>(@"SELECT *FROM vw_Billing WHERE txndate>='"+start+"' AND txndate<='"+end+"'").ToList();
        }
        [OperationContract]
        public List<vw_BankTransaction> BankTxnList(string start, string end)
        {
            return db.ExecuteStoreQuery<vw_BankTransaction>(@"SELECT *FROM vw_BankTransaction WHERE txndate BETWEEN CONVERT(datetime,'"+start+"',112) AND CONVERT(datetime,'"+end+"',112)").ToList();
        }
        [OperationContract]
        public List<vw_Customers> CustomerList(string Dist,string Quart,int type, int status, string reg, string kod, string name,string Phone)
        {
            var query = db.Customers;

            StringBuilder build = new StringBuilder();

            if (!string.IsNullOrWhiteSpace(Dist))
            {
                build.Append(" AND DistrictCode='" + Dist + "'");
            }
            if (!string.IsNullOrWhiteSpace(Quart))
            {
                build.Append(" AND QuarterCode='" + Quart + "'");
            }

            if (type > 0)
            {
                //query = query.Where(b => b.CustType==type); 
                build.Append(" AND CustType=" + (type - 1).ToString());
            }
            if (status > 0)
            {
                build.Append(" AND a.Status=" + (status - 1).ToString());
            }
            if (!string.IsNullOrWhiteSpace(reg))
            {
                build.Append(" AND UPPER(CustRegister) LIKE UPPER(N'" + reg + "%')");
            }
            if (!string.IsNullOrWhiteSpace(kod))
            {
                build.Append(" AND UPPER(CustCode) LIKE UPPER(N'" + kod + "%')");
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                build.Append(" AND UPPER(CustName) LIKE UPPER(N'" + name + "%')");
            }
            if (!string.IsNullOrWhiteSpace(Phone))
            {
                build.Append(" AND Phones LIKE '" + Phone + "%'");
            }
            if (string.IsNullOrEmpty(build.ToString()))
            {
                return db.ExecuteStoreQuery<vw_Customers>(@"SELECT a.*,a.DoorNo,b.ApartCode,b.ApartNo1,	b.ApartNo2,	b.DESCRIPTION,
	            b.QuarterName,b.DistName FROM Customer a LEFT JOIN dbo.vw_Apartment b ON a.ApartNo=b.Id ORDER BY a.CustNo DESC").ToList();
            }
            else
            {
                string where = build.ToString().Substring(5);
                return db.ExecuteStoreQuery<vw_Customers>(@"SELECT a.*,a.DoorNo,b.ApartCode,b.ApartNo1,	b.ApartNo2,	b.DESCRIPTION,
	            b.QuarterName,b.DistName FROM Customer a LEFT JOIN dbo.vw_Apartment b ON a.ApartNo=b.Id WHERE " + where + " ORDER BY a.CustNo DESC").ToList();
            }


        }
     
        #region Contract list
        [OperationContract]
        public List<vw_Contracts> ContractList(string Dist, string Quart, int BankNo, int BankBranch, string ApartNum, string ApartName, string ApartCode,
            int Section, string CustType, string CustName, string CustReg, string CustCode,
            string Collector, string ContractNo, string Toot1, string CurrProd, Boolean IsDate, DateTime dt1, DateTime dt2,string UserName,string PhoneNumber,string Mail,string BillCode)
        {
            var query = db.Customers;
            StringBuilder build = new StringBuilder();

            if (!string.IsNullOrWhiteSpace(Dist))
            {
                build.Append(" AND DistrictCode='" + Dist + "'");
            }
            if (!string.IsNullOrWhiteSpace(Quart))
            {
                build.Append(" AND QuarterCode='" + Quart + "'");
            }
            if (Section > 0)
            {
                build.Append(" AND SectionNo=" + Section);
            }

            if (BankNo > 0)
            {
                build.Append(" AND BankID=" + BankNo.ToString());
            }
            if (BankBranch > 0)
            {
                build.Append(" AND BankBranchNo=" + BankBranch.ToString());
            }
            if (!string.IsNullOrWhiteSpace(ApartNum))
            {
                build.Append(" AND ApartNo1 LIKE '" + ApartNum + "%'");
            }
            if (!string.IsNullOrWhiteSpace(ApartName))
            {
                build.Append(" AND b.Description LIKE '" + ApartName + "%'");
            }

            if (!string.IsNullOrWhiteSpace(CustType))
            {
                build.Append(" AND CustType=" + CustType);
            }
            if (!string.IsNullOrWhiteSpace(CustName))
            {
                build.Append(" AND CustName LIKE '" + CustName + "%'");
            }
            if (!string.IsNullOrWhiteSpace(CustReg))
            {
                build.Append(" AND CustRegister='" + CustReg + "'");
            }
            if (!string.IsNullOrWhiteSpace(CustCode))
            {
                build.Append(" AND CustCode='" + CustCode + "'");
            }
            if (!string.IsNullOrWhiteSpace(Collector))
            {
                build.Append(" AND Collector=" + Collector + "");
            }
            if (!string.IsNullOrWhiteSpace(ContractNo))
            {
                build.Append(" AND ContractNo='" + ContractNo + "'");
            }
            if (!string.IsNullOrWhiteSpace(Toot1))
            {
                build.Append(" AND a.DoorNo LIKE '" + Toot1 + "%'");
            }

            if (!string.IsNullOrWhiteSpace(UserName))
            {
                build.Append(" AND a.UserName LIKE '" + UserName + "%'");
            }

            if (!string.IsNullOrWhiteSpace(PhoneNumber))
            {
                build.Append(" AND Phone LIKE '" + PhoneNumber + "%'");
            }
            if (!string.IsNullOrWhiteSpace(Mail))
            {
                build.Append(" AND a.Mail LIKE '" + Mail + "%'");
            }
            if (!string.IsNullOrWhiteSpace(BillCode))
            {
                build.Append(" AND a.BillCode LIKE '" + BillCode + "%'");
            }

            if (!string.IsNullOrWhiteSpace(CurrProd))
            {
                build.Append(" AND CurrProdCode='" + CurrProd + "'");
            }
            if (IsDate)
            {
                build.Append(" AND CONVERT(CHAR(8),Contdate,112)>='" + dt1.ToString("yyyyMMdd") + "' AND CONVERT(CHAR(8),Contdate,112)<='" + dt2.ToString("yyyyMMdd") + "'");
            }
            string where = string.Empty;
            if (!string.IsNullOrEmpty(build.ToString()))
            {
                where = " WHERE " + build.ToString().Substring(5);
            }

            return db.ExecuteStoreQuery<vw_Contracts>(@"SELECT a.ContractNo,a.CurrProdCode,a.StartDate,
            a.EndDate,c.CustName,c.CustRegister,CustCode,a.DoorNo,ApartNo1,ApartNo2,b.Description,QuarterName,
            DistName, a.Balance, a.Company, a.UserName,a.Phone,a.Mail,a.BillCode,P.ProdType,a.Status,a.PauseDate,a.ContDate FROM dbo.Contract a LEFT JOIN dbo.vw_Apartment b ON a.ApartNo=b.Id
            LEFT JOIN dbo.Customer c ON a.CustNo=c.CustNo LEFT JOIN Product P ON a.CurrProdCode=P.ProdCode" + where).OrderByDescending(c => c.ContDate).ToList();
        }
        #endregion
        [OperationContract]
        public DateTime getDate()
        {
            return DateTime.Now;
        }

        [OperationContract]        
        public vw_Contracts ContractDetail(long contract_no)
        {
            using (var db1 = new SansarEntities())
            {
                db1.ContextOptions.LazyLoadingEnabled = false;
                vw_Contracts con = db1.vw_Contracts.FirstOrDefault(w => w.ContractNo == contract_no);
                return con;
            }
        }

        [OperationContract]
        public vw_Contracts ContractDetailByUser(string user_name)
        {
            using (var db1 = new SansarEntities())
            {
                db1.ContextOptions.LazyLoadingEnabled = false;
                vw_Contracts con = db1.vw_Contracts.FirstOrDefault(w => w.UserName == user_name);
                return con;
            }
            //return db.vw_Contracts.FirstOrDefault(w =>w.UserName == user_name);
        }
        [OperationContract]
        public List<vw_UtmTxn> GetUtmTxnList(DateTime txndate)
        {
            string sql = @"SELECT *FROM vw_UtmTxn "
            + " WHERE CONVERT(CHAR(8),txnStartDate,112)='" + txndate.ToString("yyyyMMdd") + "' ORDER BY JrNo";

            return db.ExecuteStoreQuery<vw_UtmTxn>(sql).ToList();
        }

        [OperationContract]
        public List<ComboItem> TransferTypes()
        {
            return db.TransactionTypes.Select(s => new ComboItem() { Display = s.Description, IntValue = s.TxnType }).ToList();
        }

        [OperationContract]
        public List<ComboItem> TransferStatus()
        {
            return db.TransactionStatus.Select(s => new ComboItem() { Display = s.Name, IntValue = s.StatusCode }).ToList();
        }

        [OperationContract]
        public List<ComboItem> TransferCategorys()
        {
            return db.TransactionCategories.Select(s => new ComboItem() { Display = s.Name, StrValue = s.TxnCategory }).ToList();
        }

        [OperationContract]
        public List<ComboItem> Banks()
        {
            return db.Banks.Select(s => new ComboItem() { Display = s.Name, IntValue = s.BankID }).ToList();
        }


        [OperationContract]
        public List<Company> CompanyList()
        {
            return db.Companies.ToList();
        }

        [OperationContract] //Бүтээгдэхүүний төрөл
        public List<ProductType> ProductTypes()
        {
            return db.ProductTypes.ToList();
        }

        [OperationContract] //Үйлчигээлний ангилал 
        public List<ItemCategory> ItemCategories()
        {
            return db.ItemCategories.ToList();
        }

        [OperationContract] //Үйлчигээлний ангилал 
        public List<Unit> Units()
        {
            return db.Units.ToList();
        }

        [OperationContract] //Хэрэглэгчийн эрхийн бүлгүүд
        public List<ComboItem> UserRoles(int UserId)
        {
            return db.UserAndRoles
                .Where(w => w.UserId == UserId)
                .Join(db.Roles, u => u.RoleId, r => r.RoleId, (u, r) => new ComboItem() { Display = r.Name, IntValue = u.RoleId })
                .ToList();
        }

        [OperationContract]
        public List<Equipment> Equipments()
        {
            return db.Equipments.Where(w => w.Status == true).ToList();
        }

        [OperationContract]
        public List<Product> Products(string prod_type)
        {
            if (string.IsNullOrEmpty(prod_type))
                return db.Products.ToList();
            else
                return db.Products.Where(w => w.ProdType == prod_type).ToList();
        }

        [OperationContract]
        public Product ProductDetail(int prod_code)
        {
            return db.Products.FirstOrDefault(w => w.ProdCode == prod_code);
        }


        [OperationContract]
        public List<Parameter> Parameters(string query)
        {
            return db.Parameters.Where(query).ToList();
        }

        [OperationContract]
        public List<User> Users(string query)
        {
            return db.Users.Where(query).ToList();
        }


    }



}

